// DBDeployer - The MySQL Sandbox
// Copyright © 2006-2020 Giuseppe Maxia
//
// Licensed under the Apache License, Version 2.0 (the "License");
// you may not use this file except in compliance with the License.
// You may obtain a copy of the License at
//
//     http://www.apache.org/licenses/LICENSE-2.0
//
// Unless required by applicable law or agreed to in writing, software
// distributed under the License is distributed on an "AS IS" BASIS,
// WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
// See the License for the specific language governing permissions and
// limitations under the License.

package sandbox

import (
	"fmt"
	"os"

	"github.com/datacharmer/dbdeployer/common"
	"github.com/datacharmer/dbdeployer/defaults"
	"github.com/datacharmer/dbdeployer/globals"
)

type TemplateDesc struct {
	TemplateInFile bool
	Description    string
	Notes          string
	Contents       string
}

type TemplateCollection map[string]TemplateDesc
type AllTemplateCollection map[string]TemplateCollection

// templates for single sandbox

var (
	initDbTemplate string = `#!{{.ShellPath}}
{{.Copyright}}
# Generated by dbdeployer {{.AppVersion}} using {{.TemplateName}} on {{.DateTime}}
export SBDIR="{{.SandboxDir}}"
export BASEDIR={{.Basedir}}
export DATADIR=$SBDIR/data
export LD_LIBRARY_PATH=$BASEDIR/lib:$BASEDIR/lib/mysql:$LD_LIBRARY_PATH
export DYLD_LIBRARY_PATH=$BASEDIR/lib:$BASEDIR/lib/mysql:$DYLD_LIBRARY_PATH

cd $SBDIR
if [ -d $DATADIR/mysql ]
then
    echo "Initialization already done."
    echo "This script should run only once."
    exit 0
fi

{{.InitScript}} \
    {{.InitDefaults}} \
    --user={{.OsUser}} \
    --basedir=$BASEDIR \
    --datadir=$DATADIR \
    --tmpdir=$SBDIR/tmp {{.ExtraInitFlags}}
exit_code=$?
if [ "$exit_code" == "0" ]
then
    echo "Database installed in $SBDIR"
else
    echo "Error installing database in $SBDIR"
    exit $exit_code
fi
{{.FixUuidFile1}}
{{.FixUuidFile2}}
`

	startTemplate string = `#!{{.ShellPath}}
{{.Copyright}}
# Generated by dbdeployer {{.AppVersion}} using {{.TemplateName}} on {{.DateTime}}
source {{.SandboxDir}}/sb_include
MYSQLD_SAFE="bin/mysqld_safe"
CUSTOM_MYSQLD={{.CustomMysqld}}
if [ -n "$CUSTOM_MYSQLD" ]
then
    CUSTOM_MYSQLD="--mysqld=$CUSTOM_MYSQLD"
fi
if [ ! -f $BASEDIR/$MYSQLD_SAFE ]
then
    echo "mysqld_safe not found in $BASEDIR/bin/"
    exit 1
fi
MYSQLD_SAFE_OK=$(sh -n $BASEDIR/$MYSQLD_SAFE 2>&1)
if [ "$MYSQLD_SAFE_OK" == "" ]
then
    if [ "$SBDEBUG" == "2" ]
    then
        echo "$MYSQLD_SAFE OK"
    fi
else
    echo "$MYSQLD_SAFE has errors"
    echo "((( $MYSQLD_SAFE_OK )))"
    exit 1
fi

TIMEOUT=180
if [ -n "$(is_running)" ]
then
    echo "sandbox server already started (found pid file $PIDFILE)"
else
    if [ -f $PIDFILE ]
    then
        # Server is not running. Removing stale pid-file
        rm -f $PIDFILE
    fi
    CURDIR=$(pwd)
    cd $BASEDIR
    $MYSQLD_SAFE --defaults-file=$SBDIR/my.sandbox.cnf $CUSTOM_MYSQLD $@ > "$SBDIR/start.log" 2>&1 &
    cd $CURDIR
    ATTEMPTS=1
    while [ ! -f $PIDFILE ]
    do
        ATTEMPTS=$(( $ATTEMPTS + 1 ))
        echo -n "."
        if [ $ATTEMPTS = $TIMEOUT ]
        then
            break
        fi
        sleep $SLEEP_TIME
        if [ -f start.log ]
        then
		    has_errors=$(grep -iw error start.log)
            if [ -n "$has_errors" ]
            then
                echo "Errors detected in start command"
                cat start.log
                break
            fi
         fi
    done
fi

if [ -f $PIDFILE ]
then
    echo " sandbox server started"
else
    echo " sandbox server not started yet"
    exit 1
fi
`
	useTemplate string = `#!{{.ShellPath}}
{{.Copyright}}
# Generated by dbdeployer {{.AppVersion}} using {{.TemplateName}} on {{.DateTime}}
source {{.SandboxDir}}/sb_include
export LD_LIBRARY_PATH=$CLIENT_LD_LIBRARY_PATH
[ -n "$TEST_REPL_DELAY" -a -f $SBDIR/data/mysql-relay.index ] && sleep $TEST_REPL_DELAY
[ -z "$MYSQL_EDITOR" ] && MYSQL_EDITOR="$CLIENT_BASEDIR/bin/mysql"
if [ ! -x $MYSQL_EDITOR ]
then
    if [ -x $SBDIR/$MYSQL_EDITOR ]
    then
        MYSQL_EDITOR=$SBDIR/$MYSQL_EDITOR
    else
        echo "MYSQL_EDITOR '$MYSQL_EDITOR' not found or not executable"
        exit 1
    fi
fi
HISTDIR={{.HistoryDir}}
[ -z "$HISTDIR" ] && export HISTDIR=$SBDIR
[ -z "$MYSQL_HISTFILE" ] && export MYSQL_HISTFILE="$HISTDIR/.mysql_history"
MY_CNF=$SBDIR/my.sandbox.cnf
MY_CNF_NO_PASSWORD=$SBDIR/my.sandbox_np.cnf
if [ -n "$NOPASSWORD" ]
then
    grep -v '^password' < $MY_CNF > $MY_CNF_NO_PASSWORD
    MY_CNF=$MY_CNF_NO_PASSWORD
fi
if [ -f $PIDFILE ]
then
    $MYSQL_EDITOR --defaults-file=$MY_CNF $MYCLIENT_OPTIONS "$@"
else
    exit 1
fi
`
	useAdminTemplate string = `#!{{.ShellPath}}
{{.Copyright}}
# Generated by dbdeployer {{.AppVersion}} using {{.TemplateName}} on {{.DateTime}}
source {{.SandboxDir}}/sb_include
$SBDIR/use -u root --host={{.SbHost}}  --port={{.AdminPort}} \
  --prompt='## ADMIN ##{{.Prompt}} [\h:{{.AdminPort}}] {\u} (\d) > '  $@
`

	sysbenchTemplate string = `#!{{.ShellPath}}
{{.Copyright}}
# Generated by dbdeployer {{.AppVersion}} using {{.TemplateName}} on {{.DateTime}}
source {{.SandboxDir}}/sb_include

[ -z "$SB_DATABASE" ] && SB_DATABASE=test

cd $SBDIR
./use -e "CREATE DATABASE IF NOT EXISTS $SB_DATABASE"
sysbench --db-driver=mysql \
	--mysql-user={{.DbUser}} \
    --mysql-password={{.DbPassword}} \
    --mysql-socket={{.SocketFile}} \
    --mysql-db=$SB_DATABASE \
    $@
`

	sysbenchReadyTemplate string = `#!{{.ShellPath}}
{{.Copyright}}
# Generated by dbdeployer {{.AppVersion}} using {{.TemplateName}} on {{.DateTime}}
source {{.SandboxDir}}/sb_include
$SBDIR/use -e 'create database if not exists sbtest'

option=$1
if [ -z "$option" ]
then
    echo "Syntax: $0 {prepare|run}"
    exit 1
fi

[ -z "$SB_DATABASE" ] && SB_DATABASE=sbtest

sysbench_version=$(sysbench --version | awk '{print $2}')

if [ -z "$sysbench_version" ]
then
    echo "sysbench version not found"
    exit 1
fi

linux_sysbench_path=/usr/share/sysbench
mac_os_sysbench_path=/usr/local/Cellar/sysbench/$sysbench_version/share/sysbench
sysbench_path=$linux_sysbench_path

if [ ! -d $sysbench_path ]
then
    sysbench_path=$mac_os_sysbench_path
fi
if [ ! -d $sysbench_path ]
then
    echo "sysbench path not found in either $linux_sysbench_path or $mac_os_sysbench_path"
    exit 1
fi

cd $SBDIR
./use -e "CREATE DATABASE IF NOT EXISTS $SB_DATABASE"
case $option in
    prepare)
      sysbench --db-driver=mysql \
	    --mysql-user={{.DbUser}} \
        --mysql-password={{.DbPassword}} \
        --mysql-socket={{.SocketFile}} \
        --mysql-db=$SB_DATABASE \
        --range_size=100 \
        --table_size=5000 \
        --tables=100 \
        --threads=1 \
        --events=0 \
        --time=60 \
        --rand-type=uniform \
        $sysbench_path/oltp_read_only.lua \
        prepare
    ;;
    run)
      sysbench --db-driver=mysql \
        --mysql-user={{.DbUser}} \
        --mysql-password={{.DbPassword}} \
        --mysql-socket={{.SocketFile}} \
        --mysql-db=sbtest \
        --range_size=100 \
        --table_size=5000 \
        --tables=100 \
        --threads=10 \
        --events=0 \
        --time=6000 \
        --rand-type=uniform \
        $sysbench_path/oltp_read_write.lua \
        --report-interval=1 run
    ;;
    *)
      echo "invalid command '$option' - accepted: prepare|run"
      exit 1
    ;;
esac
`

	mysqlshTemplate string = `#!{{.ShellPath}}
{{.Copyright}}
# Generated by dbdeployer {{.AppVersion}} using {{.TemplateName}} on {{.DateTime}}
source {{.SandboxDir}}/sb_include
export LD_LIBRARY_PATH=$CLIENT_LD_LIBRARY_PATH
[ -z "$MYSQL_SHELL" ] && MYSQL_SHELL="{{.MysqlShell}}"

[ -z "$URI" ] && URI="root:{{.DbPassword}}@{{.SbHost}}:{{.MysqlXPort}}"

if [ -f $PIDFILE ]
then
    if [ "$1" != "" ]
    then
        $MYSQL_SHELL --uri="$URI" "$*"
    else
        $MYSQL_SHELL --uri="$URI"
    fi
else
    echo "# $0 pidfile not found"
    exit 1
fi
`

	stopTemplate string = `#!{{.ShellPath}}
{{.Copyright}}
# Generated by dbdeployer {{.AppVersion}} using {{.TemplateName}} on {{.DateTime}}
source {{.SandboxDir}}/sb_include
export LD_LIBRARY_PATH=$CLIENT_LD_LIBRARY_PATH

MYSQL_ADMIN="$CLIENT_BASEDIR/bin/mysqladmin"

if [ -n "$(is_running)" ]
then
    echo "stop $SBDIR"
    # echo "$MYSQL_ADMIN --defaults-file=$SBDIR/my.sandbox.cnf $MYCLIENT_OPTIONS shutdown"
    $MYSQL_ADMIN --defaults-file=$SBDIR/my.sandbox.cnf $MYCLIENT_OPTIONS shutdown
    sleep $SLEEP_TIME
else
    if [ -f $PIDFILE ]
    then
        rm -f $PIDFILE
    fi
fi

if [ -n "$(is_running)" ]
then
    # use the send_kill script if the server is not responsive
    $SBDIR/send_kill
fi
`
	clearTemplate string = `#!{{.ShellPath}}
{{.Copyright}}
# Generated by dbdeployer {{.AppVersion}} using {{.TemplateName}} on {{.DateTime}}
source {{.SandboxDir}}/sb_include
export LD_LIBRARY_PATH=$CLIENT_LD_LIBRARY_PATH

cd $SBDIR

#
# attempt to drop databases gracefully
#

if [ -n "$(is_running)" ]
then
    for D in $(echo "show databases " | ./use -B -N | grep -v "^mysql$" | grep -iv "^information_schema$" | grep -iv "^performance_schema" | grep -ivw "^sys")
    do
        echo "set sql_mode=ansi_quotes;drop database \"$D\"" | ./use
    done
    VERSION={{.Version}}
    is_slave=$(ls data | grep relay)
    if [ -n "$is_slave" ]
    then
        ./use -e "stop slave; reset slave;"
    fi
    if [[ "$VERSION" > "5.1" ]]
    then
        for T in general_log slow_log plugin
        do
            exists_table=$(./use -e "show tables from mysql like '$T'")
            if [ -n "$exists_table" ]
            then
                ./use -e "truncate mysql.$T"
            fi
        done
    fi
fi

is_master=$(ls data | grep 'mysql-bin')
if [ -n "$is_master" ]
then
    ./use -e 'reset master'
fi

./stop
rm -f data/$(hostname)*
rm -f data/log.0*
rm -f data/*.log

#
# remove all databases if any (up to 8.0)
#
if [[ "$VERSION" < "8.0" ]]
then
    for D in $(ls -d data/*/ | grep -w -v mysql | grep -iv performance_schema | grep -ivw sys)
    do
        rm -rf $D
    done
    mkdir data/test
fi
`

	myCnfTemplate string = `
{{.Copyright}}
# Generated by dbdeployer {{.AppVersion}} using {{.TemplateName}} on {{.DateTime}}
[mysql]
prompt='{{.Prompt}} [\h:{{.Port}}] {\u} (\d) > '
#

[client]
user               = {{.DbUser}}
password           = {{.DbPassword}}
port               = {{.Port}}
socket             = {{.SocketFile}}

[mysqld]
user               = {{.OsUser}}
port               = {{.Port}}
socket             = {{.SocketFile}}
basedir            = {{.Basedir}}
datadir            = {{.Datadir}}
tmpdir             = {{.Tmpdir}}
pid-file           = {{.Datadir}}/mysql_sandbox{{.Port}}.pid
bind-address       = {{.BindAddress}}
{{.ReportHost}}
{{.ReportPort}}
log-error={{.Datadir}}/msandbox.err
{{.ServerId}}
{{.ReplOptions}}
{{.GtidOptions}}
{{.ReplCrashSafeOptions}}
{{.SemiSyncOptions}}
{{.ReadOnlyOptions}}

{{.ExtraOptions}}
`
	sendKillTemplate string = `#!{{.ShellPath}}
{{.Copyright}}
# Generated by dbdeployer {{.AppVersion}} using {{.TemplateName}} on {{.DateTime}}
source {{.SandboxDir}}/sb_include

kill_mode=$1

TIMEOUT=30

mysqld_safe_pid=$(ps auxw | grep mysqld_safe | grep "defaults-file=$SBDIR" | awk '{print $2}')
if [ -n "$(is_running)" ]
then
    MYPID=$(cat $PIDFILE)
    kill -9 $mysqld_safe_pid

    # Using one of the arguments 'crash', '-9', or 'destroy'
    # this script will terminate the database in a "pull-the-plug" fashion
    if [ "$kill_mode" == "crash" -o "$kill_mode" == "-9"  -o "$kill_mode" == "destroy" ]
    then
        echo "Terminating the server immediately --- kill -9 $MYPID"
        kill -9 $MYPID
        rm -f $PIDFILE
        rm -f $SOCKET_FILE
        for socket_file in $SOCKET_FILE $MYSQLX_SOCKET_FILE
        do
          if [ -e ${socket_file} ]
          then
            rm -f ${socket_file}
          fi
          if [ -e ${socket_file}.lock ]
          then
            rm -f ${socket_file}.lock
          fi
        done
    else
        echo "Attempting normal termination --- kill -15 $MYPID"
        kill -15 $MYPID
    fi

    # give it a chance to exit peacefully
    ATTEMPTS=1
    while [ -f $PIDFILE ]
    do
        ATTEMPTS=$(( $ATTEMPTS + 1 ))
        if [ $ATTEMPTS = $TIMEOUT ]
        then
            break
        fi
        sleep $SLEEP_TIME
    done
    if [ -f $PIDFILE ]
    then
        echo "SERVER UNRESPONSIVE --- kill -9 $MYPID"
        kill -9 $MYPID
        rm -f $PIDFILE
    fi
else
    # server not running - removing stale pid-file
    if [ -f $PIDFILE ]
    then
        rm -f $PIDFILE
    fi
fi
`
	statusTemplate string = `#!{{.ShellPath}}
{{.Copyright}}
# Generated by dbdeployer {{.AppVersion}} using {{.TemplateName}} on {{.DateTime}}
source {{.SandboxDir}}/sb_include
export LD_LIBRARY_PATH=$CLIENT_LD_LIBRARY_PATH

baredir=$(basename $SBDIR)

node_status=off
exit_code=0
if [ -f $PIDFILE -a -z "$SB_MOCKING" ]
then
    MYPID=$(cat $PIDFILE)
    running=$(ps -p $MYPID | grep $MYPID)
    if [ -n "$running" ]
    then
        node_status=on
        exit_code=0
    fi
fi
echo "$baredir $node_status"
exit $exit_code
`
	restartTemplate string = `#!{{.ShellPath}}
{{.Copyright}}
# Generated by dbdeployer {{.AppVersion}} using {{.TemplateName}} on {{.DateTime}}
source {{.SandboxDir}}/sb_include

$SBDIR/stop
$SBDIR/start $@
`
	loadGrantsTemplate string = `#!{{.ShellPath}}
{{.Copyright}}
# Generated by dbdeployer {{.AppVersion}} using {{.TemplateName}} on {{.DateTime}}
source {{.SandboxDir}}/sb_include
export LD_LIBRARY_PATH=$CLIENT_LD_LIBRARY_PATH

SOURCE_SCRIPT=$1
if [ -z "$SOURCE_SCRIPT" ]
then
    SOURCE_SCRIPT=grants.mysql
fi
PRE_GRANT_SCRIPTS="grants.mysql pre_grants.sql"
if [ -n "$(echo $PRE_GRANT_SCRIPTS | grep $SOURCE_SCRIPT)" ]
then
    export NOPASSWORD=1
fi
if [ -n "$NOPASSWORD" ]
then
    MYSQL="$CLIENT_BASEDIR/bin/mysql --no-defaults --socket=$SOCKET_FILE --port={{.Port}}"
else
    MYSQL="$CLIENT_BASEDIR/bin/mysql --defaults-file=$SBDIR/my.sandbox.cnf"
fi
VERBOSE_SQL=''
[ -n "$SBDEBUG" ] && VERBOSE_SQL=-v
if [ ! -f $SBDIR/$SOURCE_SCRIPT ]
then
    # [ -n "$VERBOSE_SQL" ] && echo "$SBDIR/$SOURCE_SCRIPT not found"
    exit 0
fi
# echo "$MYSQL -u root -t $VERBOSE_SQL < $SBDIR/$SOURCE_SCRIPT"
$MYSQL -u root -t $VERBOSE_SQL < $SBDIR/$SOURCE_SCRIPT
`
	grantsTemplate5x string = `
# Generated by dbdeployer {{.AppVersion}} using {{.TemplateName}} on {{.DateTime}}
use mysql;
set password=password('{{.DbPassword}}');
grant all on *.* to {{.DbUser}}@'{{.RemoteAccess}}' identified by '{{.DbPassword}}';
grant all on *.* to {{.DbUser}}@'localhost' identified by '{{.DbPassword}}';
grant SELECT,INSERT,UPDATE,DELETE,CREATE,DROP,INDEX,ALTER,
    SHOW DATABASES,CREATE TEMPORARY TABLES,LOCK TABLES, EXECUTE
    on *.* to msandbox_rw@'localhost' identified by '{{.DbPassword}}';
grant SELECT,INSERT,UPDATE,DELETE,CREATE,DROP,INDEX,ALTER,
    SHOW DATABASES,CREATE TEMPORARY TABLES,LOCK TABLES, EXECUTE
    on *.* to msandbox_rw@'{{.RemoteAccess}}' identified by '{{.DbPassword}}';
grant SELECT,EXECUTE on *.* to msandbox_ro@'{{.RemoteAccess}}' identified by '{{.DbPassword}}';
grant SELECT,EXECUTE on *.* to msandbox_ro@'localhost' identified by '{{.DbPassword}}';
grant REPLICATION SLAVE on *.* to {{.RplUser}}@'{{.RemoteAccess}}' identified by '{{.RplPassword}}';
# delete from user where password='';
delete from user where user='';
delete from user where user='root' and password='';
delete from db where user='';
flush privileges;
create database if not exists test;
`
	grantsTemplate57 string = `
# Generated by dbdeployer {{.AppVersion}} using {{.TemplateName}} on {{.DateTime}}
use mysql;
set password='{{.DbPassword}}';

create user {{.DbUser}}@'{{.RemoteAccess}}' identified by '{{.DbPassword}}';
grant all on *.* to {{.DbUser}}@'{{.RemoteAccess}}' ;

create user {{.DbUser}}@'localhost' identified by '{{.DbPassword}}';
grant all on *.* to {{.DbUser}}@'localhost';

create user msandbox_rw@'localhost' identified by '{{.DbPassword}}';
grant SELECT,INSERT,UPDATE,DELETE,CREATE,DROP,INDEX,ALTER,
    SHOW DATABASES,CREATE TEMPORARY TABLES,LOCK TABLES, EXECUTE
    on *.* to msandbox_rw@'localhost';

create user msandbox_rw@'{{.RemoteAccess}}' identified by '{{.DbPassword}}';
grant SELECT,INSERT,UPDATE,DELETE,CREATE,DROP,INDEX,ALTER,
    SHOW DATABASES,CREATE TEMPORARY TABLES,LOCK TABLES, EXECUTE
    on *.* to msandbox_rw@'{{.RemoteAccess}}';

create user msandbox_ro@'{{.RemoteAccess}}' identified by '{{.DbPassword}}';
create user msandbox_ro@'localhost' identified by '{{.DbPassword}}';
create user {{.RplUser}}@'{{.RemoteAccess}}' identified by '{{.RplPassword}}';
grant SELECT,EXECUTE on *.* to msandbox_ro@'{{.RemoteAccess}}';
grant SELECT,EXECUTE on *.* to msandbox_ro@'localhost';
grant REPLICATION SLAVE on *.* to {{.RplUser}}@'{{.RemoteAccess}}';
create schema if not exists test;
`

	grantsTaskUserTemplate string = `
# Generated by dbdeployer {{.AppVersion}} using {{.TemplateName}} on {{.DateTime}}

create user {{.TaskUser}}@'{{.RemoteAccess}}' identified by '{{.DbPassword}}';
create user {{.TaskUser}}@'localhost' identified by '{{.DbPassword}}';
grant {{.TaskUserRole}} to {{.TaskUser}}@'{{.RemoteAccess}}';
grant {{.TaskUserRole}} to {{.TaskUser}}@'localhost';
set default role {{.TaskUserRole}} to {{.TaskUser}}@'{{.RemoteAccess}}';
set default role {{.TaskUserRole}} to {{.TaskUser}}@'localhost';
`

	grantsTemplate8x string = `
# Generated by dbdeployer {{.AppVersion}} using {{.TemplateName}} on {{.DateTime}}
use mysql;
set password='{{.DbPassword}}';

create role if not exists R_DO_IT_ALL;
create role if not exists R_READ_WRITE;
create role if not exists R_READ_ONLY;
create role if not exists R_REPLICATION;
create role if not exists {{.CustomRoleName}};

grant {{.CustomRolePrivileges}} on {{.CustomRoleTarget}} to {{.CustomRoleName}} {{.CustomRoleExtra}};
grant all on *.* to R_DO_IT_ALL;
grant SELECT,INSERT,UPDATE,DELETE,CREATE,DROP,INDEX,ALTER,
    SHOW DATABASES,CREATE TEMPORARY TABLES,LOCK TABLES, EXECUTE
    on *.* to R_READ_WRITE;
grant SELECT,EXECUTE on *.* to R_READ_ONLY;
grant REPLICATION SLAVE on *.* to R_REPLICATION;

create user if not exists {{.DbUser}}@'{{.RemoteAccess}}' identified by '{{.DbPassword}}';
create user if not exists {{.DbUser}}@'localhost' identified by '{{.DbPassword}}';

grant {{.DefaultRole}} to {{.DbUser}}@'{{.RemoteAccess}}' ;
set default role {{.DefaultRole}} to {{.DbUser}}@'{{.RemoteAccess}}';

grant {{.DefaultRole}} to {{.DbUser}}@'localhost' ;
set default role {{.DefaultRole}} to {{.DbUser}}@'localhost';

create user if not exists msandbox_rw@'localhost' identified by '{{.DbPassword}}';
create user if not exists msandbox_rw@'{{.RemoteAccess}}' identified by '{{.DbPassword}}';

grant R_READ_WRITE to msandbox_rw@'localhost';
set default role R_READ_WRITE to msandbox_rw@'localhost';
grant R_READ_WRITE to msandbox_rw@'{{.RemoteAccess}}';
set default role R_READ_WRITE to msandbox_rw@'{{.RemoteAccess}}';

create user if not exists msandbox_ro@'{{.RemoteAccess}}' identified by '{{.DbPassword}}';
create user if not exists msandbox_ro@'localhost' identified by '{{.DbPassword}}';
create user if not exists {{.RplUser}}@'{{.RemoteAccess}}' identified by '{{.RplPassword}}';

grant R_READ_ONLY to msandbox_ro@'{{.RemoteAccess}}';
set default role R_READ_ONLY to msandbox_ro@'{{.RemoteAccess}}';

grant R_READ_ONLY to msandbox_ro@'localhost';
set default role R_READ_ONLY to msandbox_ro@'localhost';

grant R_REPLICATION to {{.RplUser}}@'{{.RemoteAccess}}';
set default role R_REPLICATION to {{.RplUser}}@'{{.RemoteAccess}}';

create schema if not exists test;

{{.TaskUserGrants}}
`

	addOptionTemplate string = `#!{{.ShellPath}}
{{.Copyright}}
# Generated by dbdeployer {{.AppVersion}} using {{.TemplateName}} on {{.DateTime}}
source {{.SandboxDir}}/sb_include
export LD_LIBRARY_PATH=$CLIENT_LD_LIBRARY_PATH

curdir=$SBDIR
cd $curdir

if [ -z "$*" ]
then
    echo "# Syntax $0 options-for-my.cnf [more options] "
    exit
fi

CHANGED=''
NO_RESTART=''

for OPTION in $@
do
    # Users can choose to skip restart if they use one of the
    # following keywords on the command line
    if [ "$OPTION" == "NORESTART" -o "$OPTION" == "NO_RESTART" -o "$OPTION" == "SKIP_RESTART" ]
    then
        NO_RESTART=1
        continue
    fi
    option_exists=$(grep $OPTION ./my.sandbox.cnf)
    if [ -z "$option_exists" ]
    then
        echo "$OPTION" >> my.sandbox.cnf
        echo "# option '$OPTION' added to configuration file"
        CHANGED=1
    else
        echo "# option '$OPTION' already exists in configuration file"
    fi
done

if [ -n "$CHANGED" -a -z "$NO_RESTART" ]
then
    ./restart
fi
`
	showLogTemplate string = `#!{{.ShellPath}}
{{.Copyright}}
# Generated by dbdeployer {{.AppVersion}} using {{.TemplateName}} on {{.DateTime}}
source {{.SandboxDir}}/sb_include

cd $SBDIR

if [ ! -d ./data ]
then
    echo "$SBDIR/data not found"
    exit 1
fi

log=$1
[ -z "$log" ] && log='err'
function get_help {
    exit_code=$1
    [ -z "$exit_code" ] && exit_code=0
    echo "# Usage: $0 [log] "
    echo "# Where 'log' is one of 'err' (error log),  'gen' (general log)"
    echo "# Or it can be a variable name that identifies a log"
    echo "# (The default is 'err')"
    exit $exit_code
}

if [ "$log" == "-h" -o "$log" == "--help" -o "$log" == "-help" -o "$log" == "help" ]
then
    get_help 0
fi

check_output

case $log in
    err)
        target=$SBDIR/data/msandbox.err
        ;;
    gen)
        target=$($SBDIR/use -BN -e "show variables like 'general_log_file'" | awk '{print $2}')
        ;;
    slow)
        target=$SBDIR/data/slow_log.data
        ;;
    *)
        target=$($SBDIR/use -BN -e "show variables like '$log'" | awk '{print $2}')
        ;;
esac

if [ -z "$target" ]
then
    echo "target not set"
    exit 1
fi
if [ ! -f $target ]
then
    echo "Log file '$target' not found"
    exit 1
fi

if [ -n "$pager" ]
then
    (printf "#\n# Showing $target\n#\n" ; cat $target ) | $pager
else
    (printf "#\n# Showing $target\n#\n" ; cat $target )
fi
`
	showBinlogTemplate string = `#!{{.ShellPath}}
{{.Copyright}}
# Generated by dbdeployer {{.AppVersion}} using {{.TemplateName}} on {{.DateTime}}
source {{.SandboxDir}}/sb_include
export LD_LIBRARY_PATH=$CLIENT_LD_LIBRARY_PATH

curdir=$SBDIR
cd $curdir

if [ ! -d ./data ]
then
    echo "$curdir/data not found"
    exit 1
fi

pattern=$1
[ -z "$pattern" ] && pattern='[0-9]*'
function get_help {
    exit_code=$1
    [ -z "$exit_code" ] && exit_code=0
    echo "# Usage: $0 [BINLOG_PATTERN] "
    echo "# Where BINLOG_PATTERN is a number, or part of a number used after 'mysql-bin'"
    echo "# (The default is '[0-9]*]')"
    echo "# examples:"
    echo "#          ./show_binlog 000001 | less "
    echo "#          ./show_binlog 000012 | vim - "
    echo "#          ./show_binlog  | grep -i 'CREATE TABLE'"
    exit $exit_code
}

if [ "$pattern" == "-h" -o "$pattern" == "--help" -o "$pattern" == "-help" -o "$pattern" == "help" ]
then
    get_help 0
fi
# set -x
last_binlog=$(ls -lotr data/mysql-bin.$pattern | tail -n 1 | awk '{print $NF}')

if [ -z "$last_binlog" ]
then
    echo "No binlog found in $curdir/data"
    get_help 1
fi

check_output

if [ -n "$pager" ]
then
    (printf "#\n# Showing $last_binlog\n#\n" ; ./my sqlbinlog --verbose $last_binlog ) | $pager
else
    (printf "#\n# Showing $last_binlog\n#\n" ; ./my sqlbinlog --verbose $last_binlog )
fi
`
	myTemplate string = `#!{{.ShellPath}}
{{.Copyright}}
# Generated by dbdeployer {{.AppVersion}} using {{.TemplateName}} on {{.DateTime}}
source {{.SandboxDir}}/sb_include
export LD_LIBRARY_PATH=$CLIENT_LD_LIBRARY_PATH

if [ "$1" = "" ]
then
    echo "syntax my sql{dump|binlog|admin} arguments"
    exit
fi

MYSQL=$CLIENT_BASEDIR/bin/mysql

SUFFIX=$1
shift

MYSQLCMD="$CLIENT_BASEDIR/bin/my$SUFFIX"

NODEFAULT=(myisam_ftdump
myisamlog
mysql_config
mysql_convert_table_format
mysql_find_rows
mysql_fix_extensions
mysql_fix_privilege_tables
mysql_secure_installation
mysql_setpermission
mysql_tzinfo_to_sql
mysql_config_editor
mysql_waitpid
mysql_zap
mysqlaccess
mysqlbinlog
mysqlbug
mysqldumpslow
mysqlhotcopy
mysqltest
mysqlsh
mysqltest_embedded)

DEFAULTSFILE="--defaults-file=$SBDIR/my.sandbox.cnf"

for NAME in ${NODEFAULT[@]}
do
    if [ "my$SUFFIX" = "$NAME" ]
    then
        DEFAULTSFILE=""
        break
    fi
done

if [ -f $MYSQLCMD ]
then
    $MYSQLCMD $DEFAULTSFILE "$@"
else
    echo "$MYSQLCMD not found "
fi
`
	showRelaylogTemplate string = `#!{{.ShellPath}}
{{.Copyright}}
# Generated by dbdeployer {{.AppVersion}} using {{.TemplateName}} on {{.DateTime}}
source {{.SandboxDir}}/sb_include
export LD_LIBRARY_PATH=$CLIENT_LD_LIBRARY_PATH
curdir=$SBDIR
cd $curdir

if [ ! -d ./data ]
then
    echo "$curdir/data not found"
    exit 1
fi
relay_basename=$1
[ -z "$relay_basename" ] && relay_basename='mysql-relay'
pattern=$2
[ -z "$pattern" ] && pattern='[0-9]*'
function get_help {
    exit_code=$1
    [ -z "$exit_code" ] && exit_code=0
    echo "# Usage: $0 [ relay-base-name [BINLOG_PATTERN]] "
    echo "# Where relay-basename is the initial part of the relay ('$relay_basename')"
    echo "# and BINLOG_PATTERN is a number, or part of a number used after '$relay_basename'"
    echo "# (The default is '[0-9]*]')"
    echo "# examples:"
    echo "#          ./show_relaylog relay-log-alpha 000001 | less "
    echo "#          ./show_relaylog relay-log 000012 | vim - "
    echo "#          ./show_relaylog  | grep -i 'CREATE TABLE'"
    exit $exit_code
}

if [ "$pattern" == "-h" -o "$pattern" == "--help" -o "$pattern" == "-help" -o "$pattern" == "help" ]
then
    get_help 0
fi
# set -x
last_relaylog=$(ls -lotr data/$relay_basename.$pattern | tail -n 1 | awk '{print $NF}')

if [ -z "$last_relaylog" ]
then
    echo "No relay log found in $curdir/data"
    get_help 1
fi

check_output

if [ -n "$pager" ]
then
    (printf "#\n# Showing $last_relaylog\n#\n" ; ./my sqlbinlog --verbose $last_relaylog ) | $pager
else
   (printf "#\n# Showing $last_relaylog\n#\n" ; ./my sqlbinlog --verbose $last_relaylog )
fi
`
	testSbTemplate string = `#!{{.ShellPath}}
{{.Copyright}}
# Generated by dbdeployer {{.AppVersion}} using {{.TemplateName}} on {{.DateTime}}
source {{.SandboxDir}}/sb_include
export LD_LIBRARY_PATH=$CLIENT_LD_LIBRARY_PATH
cd $SBDIR

fail=0
pass=0
TIMEOUT=180
expected_port={{.Port}}
expected_version=$(echo "{{.Version}}" | tr -d 'A-Z,a-z,_-')


if [ -f sbdescription.json ]
then
    sb_single=$(grep 'type' sbdescription.json| grep 'single')
fi

function test_query {
    user=$1
    query="$2"
    expected=$3
    ./use -BN -u $user -e "$query" > /dev/null 2>&1
    exit_code=$?
    if [ "$exit_code" == "$expected" ]
    then
        msg="was successful"
        if [ "$expected" != "0" ]
        then
            msg="failed as expected"
        fi
        echo "ok - query $msg for user $user: '$query'"
        pass=$((pass+1))
    else
        echo "not ok - query failed for user $user: '$query'"
        fail=$((fail+1))
    fi
}

if [ -n "$CHECK_LOGS" ]
then
    log_has_errors=$(grep ERROR $SBDIR/data/msandbox.err)
    if [ -z "$log_has_errors" ]
    then
        echo "ok - no errors in log"
        pass=$((pass+1))
    else
        echo "not ok - errors found in log"
        fail=$((fail+1))
    fi
fi

if [ -z "$(is_running)" ]
then
    echo "not ok - server stopped"
    fail=$((fail+1))
else
    version=$(./use -BN -e "select version()")
    port=$(./use -BN -e "show variables like 'port'" | awk '{print $2}')
    if [ -n "$version" ]
    then
        echo "ok - version '$version'"
        pass=$((pass+1))
    else
        echo "not ok - no version detected"
        fail=$((fail+1))
    fi
    if [ -n "$port" ]
    then
        echo "ok - port detected: $port"
        pass=$((pass+1))
    else
        echo "not ok - no port detected"
        fail=$((fail+1))
    fi
    
    if [ -n "$( echo $version| grep $expected_version)" ]
    then
        echo "ok - version is $version as expected"
        pass=$((pass+1))
    else
        echo "not ok - version detected ($version) but expected was $expected_version"
        fail=$((fail+1))
    fi
    if [ "$port" == "$expected_port" ]
    then
        echo "ok - port is $port as expected"
        pass=$((pass+1))
    else
        echo "not ok - port detected ($port) but expected was $expected_port"
        fail=$((fail+1))
    fi
    if [[ $MYSQL_VERSION_MAJOR -ge 5 ]]
    then
        ro_query='use mysql; select count(*) from information_schema.tables where table_schema=schema()'
    else
        ro_query='show tables from mysql'
    fi
    create_query='create table if not exists test.txyz(i int)'
    drop_query='drop table if exists test.txyz'
    test_query msandbox_ro 'select 1' 0
    test_query msandbox_rw 'select 1' 0
    test_query msandbox_ro "$ro_query" 0
    test_query msandbox_rw "$ro_query" 0
    if [ -n "$sb_single" ]
    then
        test_query msandbox_ro "$create_query" 1
        test_query msandbox_rw "$create_query" 0
        test_query msandbox_rw "$drop_query" 0
    fi
fi
fail_label="fail"
pass_label="PASS"
exit_code=0
tests=$(($pass+$fail))
if [ "$fail" != "0" ]
then
    fail_label="FAIL"
    pass_label="pass"
    exit_code=1
fi
printf "# Tests : %5d\n" $tests
printf "# $pass_label  : %5d \n" $pass
printf "# $fail_label  : %5d \n" $fail
exit $exit_code
`
	replicationOptions string = `
# basic replication options
relay-log-index=mysql-relay
relay-log=mysql-relay
log-bin=mysql-bin
`
	semisyncMasterOptions string = `
# semi-synchronous replication options for master
plugin-load=rpl_semi_sync_master=semisync_master.so
#rpl_semi_sync_master_enabled=1
`
	semisyncSlaveOptions string = `
# semi-synchronous replication options for slave
plugin-load=rpl_semi_sync_slave=semisync_slave.so
#rpl_semi_sync_slave_enabled=1
`
	replCrashSafeOptions string = `
# replication crash-safe options
master-info-repository=table
relay-log-info-repository=table
relay-log-recovery=on
`
	gtidOptions56 string = `
# GTID options for 5.6
gtid_mode=ON
log-slave-updates
enforce-gtid-consistency
`
	gtidOptions57 string = `
# GTID options for 5.7 +
gtid_mode=ON
enforce-gtid-consistency
`
	exposeDdTables string = `
set persist debug='+d,skip_dd_table_access_check';
set @col_type=(select c.type from mysql.columns c inner join mysql.tables t where t.id=table_id and t.name='tables' and c.name='hidden');
set @visible=(if(@col_type = 'MYSQL_TYPE_ENUM', 'Visible', '0'));
set @hidden=(if(@col_type = 'MYSQL_TYPE_ENUM', 'System', '1'));
create table sys.dd_hidden_tables (id bigint unsigned not null primary key, name varchar(64), schema_id bigint unsigned);
insert into sys.dd_hidden_tables select id, name, schema_id from mysql.tables where hidden=@hidden;
update mysql.tables set hidden=@visible where hidden=@hidden and schema_id = 1
`

	sbLockedTemplate string = `#!{{.ShellPath}}
{{.Copyright}}
# Generated by dbdeployer {{.AppVersion}} using {{.TemplateName}} on {{.DateTime}}
echo "This sandbox is locked."
echo "The '{{.ClearCmd}}' command has been disabled."
echo "The contents of the old '{{.ClearCmd}}' command are in the '{{.NoClearCmd}}' file"
echo 'To remove the lock, run "dbdeployer admin unlock {{.SandboxDir}}"'
`
	noOpMockTemplate string = `#!{{.ShellPath}}
# The purpose of this script is to run mock tests with a
# command that returns a wanted exit code
exit_code=0
 
# The calling procedure can set FAILMOCK to
# force a failing result.
if [ -n "$FAILMOCK" ]
then
    exit_code=$FAILMOCK
fi
# If MOCKMSG is set, the script will display its contents
if [ -n "$MOCKMSG" ]
then
    echo $MOCKMSG
fi

# If MOCKARGS is set, the script will display its arguments
if [ -n "$MOCKARGS" ]
then
    echo "[$exit_code] $0 $@"
fi
exit $exit_code`

	mysqldSafeMockTemplate string = `#!{{.ShellPath}}
# This script mimics the minimal behavior of mysqld_safe
# so that we can run tests for dbdeployer without using the real
# MySQL binaries.
defaults_file=$1
no_defaults_error="No valid defaults file provided: use --defaults-file=filename"
if [ -z "$defaults_file" ]
then
    echo "$no_defaults_error"
    exit 1
fi
valid_defaults=$(echo "$defaults_file" | grep '\--defaults-file')
if [ -z "$valid_defaults" ]
then
    echo "$no_defaults_error"
    exit 1
fi
defaults_file=$(echo $defaults_file| sed 's/--defaults-file=//')

if [ ! -f "$defaults_file" ]
then
    echo "defaults file $defaults_file not found"
    exit 1
fi

pid_file=$(grep pid-file $defaults_file | awk '{print $3}')

if [ -z "$pid_file" ]
then
    echo "PID file not found in  $defaults_file"
    exit 1
fi

touch $pid_file

exit 0
`

	tidbMockTemplate string = `#!{{.ShellPath}}
# This script mimics the minimal behavior of tidb-server
# so that we can run tests for dbdeployer without using the real
# TiDB binaries.
config=$1
if [ -z "$config" ]
then
    echo "No defaults file provided: use -config filename"
    exit 1
fi
valid_config=$(echo $config | grep '\-config')
if [ -z "$valid_config" ]
then
    echo "Not a valid config spec"
    exit 1
fi

config_file=$2

if [ -z "$config_file" ]
then
    echo "No configuration file provided"
    exit 1
fi

if [ ! -f "$config_file" ]
then
    echo "config file $config_file not found"
    exit 1
fi

socket_file=$(grep "socket\s*=" $config_file | awk '{print $3}' | tr -d '"')

if [ -z "$socket_file" ]
then
    echo "socket file not found in  $config_file"
    exit 1
fi

touch $socket_file
sleep 1
exit 0
`

	afterStartTemplate string = `#!{{.ShellPath}}
{{.Copyright}}
# Generated by dbdeployer {{.AppVersion}} using {{.TemplateName}} on {{.DateTime}}
source {{.SandboxDir}}/sb_include

# Modify this template to run commands that you want to execute
# after the database is started
exit 0
`

	sbIncludeTemplate string = `
export SBDIR="{{.SandboxDir}}"
export BASEDIR={{.Basedir}}
export CLIENT_BASEDIR={{.ClientBasedir}}
export MYSQL_VERSION={{.Version}}
export MYSQL_SORTABLE_VERSION={{.SortableVersion}}
export MYSQL_VERSION_MAJOR={{.VersionMajor}}
export MYSQL_VERSION_MINOR={{.VersionMinor}}
export MYSQL_VERSION_REV={{.VersionRev}}
export MYSQL_PORT={{.Port}}
export MYSQLX_PORT={{.MysqlXPort}}
export MYSQLX_SOCKET_FILE={{.MysqlXSocket}}
export ADMIN_PORT={{.AdminPort}}
export FLAVOR={{.Flavor}}
export SANDBOX_TYPE={{.SandboxType}}
export SERVER_ID={{.ServerId}}
export SBHOST={{.SbHost}}
export DATADIR=$SBDIR/data
export LD_LIBRARY_PATH=$BASEDIR/lib:$BASEDIR/lib/mysql:$LD_LIBRARY_PATH
export CLIENT_LD_LIBRARY_PATH=$CLIENT_BASEDIR/lib:$CLIENT_BASEDIR/lib/mysql:$LD_LIBRARY_PATH
export DYLD_LIBRARY_PATH=$BASEDIR/lib:$BASEDIR/lib/mysql:$DYLD_LIBRARY_PATH
export CLIENT_DYLD_LIBRARY_PATH=$CLIENT_BASEDIR/lib:$CLIENT_BASEDIR/lib/mysql:$DYLD_LIBRARY_PATH
export PIDFILE=$SBDIR/data/mysql_sandbox{{.Port}}.pid
export SOCKET_FILE={{.SocketFile}}
[ -z "$SANDBOX_HOME" ] && export SANDBOX_HOME=$HOME/sandboxes
[ -z "$SANDBOX_BINARY" ] && export SANDBOX_BINARY=$HOME/opt/mysql
[ -z "$SLEEP_TIME" ] && export SLEEP_TIME=1

# dbdeployer is not compatible with .mylogin.cnf,
# as it bypasses --defaults-file and --no-defaults.
# See: https://dev.mysql.com/doc/refman/8.0/en/mysql-config-editor.html
# The following statement disables .mylogin.cnf
export MYSQL_TEST_LOGIN_FILE=/tmp/dont_break_my_sandboxes$RANDOM

function is_running
{
    if [ -f $PIDFILE ]
    then
        MYPID=$(cat $PIDFILE)
        ps -p $MYPID | grep $MYPID
    fi
}

function check_output
{
    # Checks if the output is a terminal or a pipe
    if [  -t 1 ]
    then
        echo "###################### WARNING ####################################"
        echo "# You are not using a pager."
        echo "# The output of this script can be quite large."
        echo "# Please pipe this script with a pager, such as 'less' or 'vim -'"
        echo "# Choose one of the following:"
        echo "#     * simply RETURN to continue without a pager"
        echo "#     * 'q' to exit "
        echo "#     * enter the name of the pager to use"
        read answer
        case $answer in
            q)
            exit
            ;;
            *)
            unset pager
            [ -n "$answer" ] && pager=$answer
            ;;
        esac
    fi
}
`
	connectionInfoSql string = `
CHANGE MASTER TO master_host="{{.SbHost}}",
master_port={{.Port}},
master_user="{{.RplUser}}",
master_password="{{.RplPassword}}"
`
	ConnectionInfoJson string = `
{
    "master_host" : "{{.SbHost}}",
    "master_port" : {{.Port}},
    "master_user" : "{{.RplUser}}",
    "master_password" : "{{.RplPassword}}"
}
`
	ConnectionInfoConf string = `
master_host = {{.SbHost}}
master_port = {{.Port}}
master_user = {{.RplUser}}
master_password : {{.RplPassword}}
`

	cloneConnectionSql string = `
-- This file is used by a recipient sandbox
SET PERSIST clone_valid_donor_list="{{.SbHost}}:{{.Port}}";
CLONE INSTANCE FROM {{.DbUser}}@{{.SbHost}}:{{.Port}} IDENTIFIED BY '{{.DbPassword}}';
`
	cloneFromTemplate string = `#!{{.ShellPath}}
{{.Copyright}}
# Generated by dbdeployer {{.AppVersion}} using {{.TemplateName}} on {{.DateTime}}
source {{.SandboxDir}}/sb_include
cd $SBDIR

donor_sandbox=$1
if [ -z "$donor_sandbox" ]
then
    echo "donor sandbox name expected"
    exit 1
fi
if [ ! -d $SANDBOX_HOME/$donor_sandbox ]
then
    echo "Sandbox $SANDBOX_HOME/$donor_sandbox not found"
    exit 1
fi

[ -z "$MASTER_LABEL" ] && MASTER_LABEL=master
[ -z "$NODE_LABEL" ] && NODE_LABEL=node

# Using standard directory node names to find connection info.
# This may fail if directory names have been customized
# Use environment variables MASTER_LABEL and NODE_LABEL if directory names have changed
if [ -f $SANDBOX_HOME/$donor_sandbox/initialize_slaves ]
then
    donor_sandbox="$donor_sandbox/$MASTER_LABEL"
elif [ -f $SANDBOX_HOME/$donor_sandbox/use_all ]
then
    donor_sandbox="$donor_sandbox/${NODE_LABEL}1"
fi

donor_use=$SANDBOX_HOME/$donor_sandbox/use
donor_metadata=$SANDBOX_HOME/$donor_sandbox/metadata
if [ ! -x $donor_use ]
then
    echo "Executable $donor_use not found"
    exit 1
fi
if [ ! -x $donor_metadata ]
then
    echo "Executable $donor_metadata not found"
    exit 1
fi

donor_os=$($donor_use -BN -e 'select @@version_compile_os')
recipient_os=$(./use -BN -e 'select @@version_compile_os')
if [ "$donor_os" != "$recipient_os" ]
then
    echo "Donor OS ($donor_os) and recipient OS ($recipient_os) are different" 
    echo "A cloning pre-requisite is that both donor and recipient use the same OS"
    exit 1
fi

donor_version=$($donor_metadata version)
recipient_version=$(./metadata version)
if [ "$donor_version" != "$recipient_version" ]
then
    echo "Donor version ($donor_version) and recipient version ($recipient_version) are different" 
    echo "A cloning pre-requisite is that both donor and recipient use the same version"
    exit 1
fi

clone_connection=$SANDBOX_HOME/$donor_sandbox/clone_connection.sql

if [ ! -f $clone_connection ]
then
    echo "File $clone_connection not found"
    exit 1
fi

clone_installed=$(./use -BN -e 'show plugins' | grep clone)
if [ -z "$clone_installed" ]
then
    echo "Installing clone plugin in recipient sandbox"
    ./use -e "install plugin clone soname 'mysql_clone.so'"
fi

donor_clone_installed=$($donor_use -BN -e 'show plugins' | grep clone)
if [ -z "$donor_clone_installed" ]
then
    echo "Installing clone plugin in donor sandbox"
    $donor_use -e "install plugin clone soname 'mysql_clone.so'"
fi

echo "Cloning from $donor_sandbox"
./use < $clone_connection
if [ "$?" != "0" ]
then
    echo "error during clone procedure"
    exit 1
fi

#
# Taking username and password from the donor sandbox
#
source_dir=$SANDBOX_HOME/$donor_sandbox
user=$(grep ^user $source_dir/my.sandbox.cnf| head -n 1 | awk '{print $3}' )
password=$(grep ^password $source_dir/my.sandbox.cnf| head -n 1 | awk '{print $3}' )

dest_user_line=$(grep ^user $SBDIR/my.sandbox.cnf | head -n 1)
dest_password_line=$(grep ^password $SBDIR/my.sandbox.cnf | head -n 1)

sed -e "s/$dest_user_line/user=$user/" < $SBDIR/my.sandbox.cnf | sed -e "s/$dest_password_line/password=$password/" > new_my.sandbox.cnf
mv my.sandbox.cnf my.sandbox.cnf.old
mv new_my.sandbox.cnf my.sandbox.cnf

echo "Giving time to cloned server to restart"
timeout=30
elapsed=0
connected=
sleep 3
while [ -z "$connected" ]
do
    response=$(./use -BN -e 'select 1234')
    if [ "$response" == "1234" ]
    then
        connected=1
    else
        sleep 1
        elapsed=$((elapsed+1))
        echo -n "."
        if [[ $elapsed -ge $timeout ]]
        then
            echo "Connection from $donor_sandbox failed after cloning"
            echo "WARNING: this sandbox has the username and password of the donor sandbox"
            exit 1
        fi
    fi
done
echo ""

binlog_file=$(./use -BN -e 'select BINLOG_FILE from performance_schema.clone_status')
binlog_position=$(./use -BN -e 'select BINLOG_POSITION from performance_schema.clone_status')
if [ -z "$binlog_file" -o -z "$binlog_position" ]
then
    echo "Master binary log info not found after clone"  
    exit 1
fi
echo "MASTER_LOG_FILE=\"$binlog_file\", MASTER_LOG_POS=$binlog_position" > clone_replication.sql
`

	replicateFromTemplate string = `#!{{.ShellPath}}
{{.Copyright}}
# Generated by dbdeployer {{.AppVersion}} using {{.TemplateName}} on {{.DateTime}}
source {{.SandboxDir}}/sb_include

cd $SBDIR

active_replication=$SBDIR/active_replication
remove_replication=$SBDIR/remove_replication

master_sandbox=$1
if [ -z "$master_sandbox" ]
then
    echo "syntax $0 master_sandbox"
    exit 1
fi

with_clone=$2


if [ -f $active_replication ]
then
    echo "There is already an active replication for this sandbox"
    cat $active_replication
    echo ""
    echo "Run $remove_replication to remove the existing one."
    exit 1
fi

master_path=$SANDBOX_HOME/$master_sandbox
if [ ! -d $master_path ]
then
    echo "master sandbox $master_path not found"
    exit 1
fi

[ -z "$MASTER_LABEL" ] && MASTER_LABEL=master
[ -z "$NODE_LABEL" ] && NODE_LABEL=node

# Using standard directory node names to find connection info.
# This may fail if directory names have been customized
# Use environment variables MASTER_LABEL and NODE_LABEL if directory names have changed
if [ -f $master_path/initialize_slaves ]
then
    master_path="$master_path/$MASTER_LABEL"
elif [ -f $master_path/use_all ]
then
    master_path="$master_path/${NODE_LABEL}1"
fi

master_connection=$master_path/connection.sql
master_metadata=$master_path/metadata
master_use_script=$master_path/use

if [ ! -f $master_connection ]
then
    echo "master connection file $master_connection not found"
    exit 1
fi
if [ ! -x $master_metadata ]
then
    echo "executable master metadata $master_metadata not found"
    exit 1
fi

if [ ! -x $master_use_script ]
then
    echo "executable $master_use_script not found"
    exit 1
fi

master_server_id=$($master_use_script -BN -e 'select @@server_id')
slave_server_id=$($SBDIR/use -BN -e 'select @@server_id')

if [ "$master_server_id" == "$slave_server_id" ]
then
    echo "Both the intended master and slave have the same server_id"
    echo "To avoid this clash, deploy again either the master or the slave sandbox,"
    echo "using the option '--port-as-server-id'"
    echo "and then try again the operation."
    exit 1
fi


can_use_gtid=
using_gtid=
master_sortable_version=$($master_metadata sversion)
slave_sortable_version=$($SBDIR/metadata sversion)
minimum_version_gtid="005006008"
if [[ "v$master_sortable_version" > "v$minimum_version_gtid" ]]
then
	if [[ "v$slave_sortable_version" > "v$minimum_version_gtid" ]]
	then
  	    can_use_gtid=1
  	fi
fi  
if [ -z "$master_sortable_version" -o -z "$slave_sortable_version" ]
then
    unset can_use_gtid
fi

if [ -n "$can_use_gtid" ]
then
    master_server_uuid=$($master_use_script -BN -e 'select @@server_uuid')
    slave_server_uuid=$($SBDIR/use -BN -e 'select @@server_uuid')

    if [ "$master_server_uuid" == "$slave_server_uuid" ]
    then
        echo "Both the intended master and slave have the same server_uuid"
        echo "To avoid this clash, deploy again either the master or the slave sandbox,"
        echo "using the option '--keep-server-uuid"
        echo "and then try again the operation."
        exit 1
    fi

    master_using_gtid=$($master_use_script -BN -e "select @@gtid_mode"| grep '^ON$')
    slave_using_gtid=$($SBDIR/use -BN -e "select @@gtid_mode"| grep '^ON$')
    if [ -n "$master_using_gtid" ]
    then
        if [ -n "$slave_using_gtid" ]
        then
            using_gtid=1
        else
            echo "Only one server is using GTID. Either both or none of them should have GTID enabled"
            exit 1
        fi  
    fi
fi

if [ "$with_clone" == "clone" ]
then
    if [ ! -x ./clone_from ]
    then
        echo "clone requested, but executable 'clone_from' not found"
        exit 1
    fi
    ./clone_from $master_sandbox
    if [ "$?" != "0" ] ; then exit 1 ; fi
fi


master_short_version=$($master_metadata short)
master_major=$($master_metadata major)
master_minor=$($master_metadata minor)
slave_major=$($SBDIR/metadata major)
slave_minor=$($SBDIR/metadata minor)

if [ -n "$using_gtid" ]
then
	if [ "$master_short_version" == "5.7" -o "$master_short_version" == "5.6" ]
    then
        echo "resetting master (needed in version < 8.0)"
        $master_use_script -e "reset master"
    fi
fi

master_status=/tmp/mstatus$$

$master_use_script -e 'show master status\G' > $master_status
binlog_file=$(grep File < $master_status | awk '{print $2}')
binlog_pos=$(grep Position < $master_status | awk '{print $2}')
rm -f $master_status

if [ -z "$binlog_file" ]
then
    echo "No binlog information found in $master_path"
    exit 1
fi

if [ -z "$binlog_pos" ]
then
    echo "No binlog position found in $master_path"
    exit 1
fi

replication_ok=""
replication_error=""

# e.g master 5 and slave 8
if [[ $master_major -lt $slave_major ]]
then
    replication_ok=yes
    replication_error=""
fi

# e.g. master 8 and slave 5
if [[ $master_major -gt $slave_major ]]
then
    replication_ok=no
    replication_error="Master major version should be lower than slave version (or equal)"
fi

# e.g. master 5.6 with slave 5.7 or same version for both
if [[ $master_major -eq $slave_major ]]
then
    if [[ $master_minor -le $slave_minor ]]
    then
        replication_ok=yes
        replication_error=""
    else
        # e.g. master 5.7 with slave 5.6
        replication_ok=no
        replication_error="Master minor version should be lower than slave version (or equal)"
    fi
fi

if [ "$replication_ok" != "yes" ]
then
    echo $replication_error
    exit 1
fi

if [ -n "$using_gtid" ]
then
    connection_string=$(cat $master_connection ; echo -n ", master_auto_position=1")
else 
    connection_string=$(cat $master_connection ; echo -n ', master_log_file="'$binlog_file'", master_log_pos='$binlog_pos )
    if [ -f clone_replication.sql ]
    then
        connection_string=$(cat $master_connection ; echo -n ", " ; cat clone_replication.sql)
    fi
fi

# If master is 8.0, the slave must be at least 8.0
if [ "$master_short_version" == "8.0" ]
then
    connection_string="$connection_string, GET_MASTER_PUBLIC_KEY=1"
fi

echo "Connecting to $master_path"

$SBDIR/use -v -e "$connection_string"
if [ "$?" != "0" ] ; then exit 1 ; fi

if [ -f clone_replication.sql ]
then
    rm -f clone_replication.sql
fi
$SBDIR/use -v -e 'start slave'
$SBDIR/use -v -e 'SHOW SLAVE STATUS\G' | grep "\(Running:\|Master_Log_Pos\|\<Master_Log_File\|Retrieved\|Executed\|Auto_Position\)"
date > $active_replication
echo "Connected to $master_path" >> $active_replication
echo "#!{{.ShellPath}}" > $remove_replication
echo "$SBDIR/use -v -e 'stop slave; reset slave'" >> $remove_replication
echo "rm -f $active_replication" >> $remove_replication
chmod +x $remove_replication

`
	metadataTemplate string = `#!{{.ShellPath}}
{{.Copyright}}
# Generated by dbdeployer {{.AppVersion}} using {{.TemplateName}} on {{.DateTime}}
source {{.SandboxDir}}/sb_include

request=$1

#
# This script has the purpose of producing useful
# metadata about the sandbox without need of
# querying the database server.
# The script output can be used by other shell scripts
# to gather information needed for decision making.
#
function show_help {
    exit_code=$1
    echo "Syntax: $0 request"
    echo "Available requests:"
    echo "  version"
    echo "  major"
    echo "  minor"
    echo "  rev"
    echo "  short (= major.minor)"
    echo "  sversion (=sortable version string)"
    echo ""
    echo "  host"
    echo "  basedir"
    echo "  cbasedir (Client Basedir)"
    echo "  datadir"
    echo "  port"
    echo "  xport (MySQLX port)"
    echo "  aport (Admin port)"
    echo "  socket"
    echo "  serverid (server id)"
    echo "  pid (Process ID)"
    echo "  pidfile (PID file)"
    echo "  flavor"
    echo "  sbhome (SANDBOX_HOME)"
    echo "  sbbin (SANDBOX_BINARY)"
    echo "  sbtype (Sandbox Type)"
    exit $exit_code
}

case $request in
    version)
        echo $MYSQL_VERSION
        ;;
    major)
        echo $MYSQL_VERSION_MAJOR
        ;;
    minor)
        echo $MYSQL_VERSION_MINOR
        ;;
    rev)
        echo $MYSQL_VERSION_REV
        ;;
    short)
        echo "$MYSQL_VERSION_MAJOR.$MYSQL_VERSION_MINOR"
        ;;
    sversion)
        echo "$MYSQL_SORTABLE_VERSION"
        ;;
    host)
        echo "$SBHOST"
        ;;
    datadir)
        echo "$DATADIR"
        ;;
    port)
        echo "$MYSQL_PORT"
        ;;
    serverid)
        if [ -n "$(is_running)" ]
        then
            $SBDIR/use -BN -e "select @@server_id"
        else
            echo "$SERVER_ID"
        fi
        ;;
    xport)
        echo "$MYSQLX_PORT"
        ;;
    aport)
        echo "$ADMIN_PORT"
        ;;
    socket)
        echo "$SOCKET_FILE"
        ;;
    pidfile)
        echo "$PIDFILE"
        ;;
    pid)
        if [ -f "$PIDFILE" ]
        then
            cat $PIDFILE
        fi
        ;;
    basedir)
        echo "$BASEDIR"
        ;;
    cbasedir)
        echo "$CLIENT_BASEDIR"
        ;;
    sbhome)
        echo "$SANDBOX_HOME"
        ;;
    sbbin)
        echo "$SANDBOX_BINARY"
        ;;
    flavor)
        echo "$FLAVOR"
        ;;
    sbtype)
        echo "$SANDBOX_TYPE"
        ;;
    help)
        show_help 0
        ;;
    *)
        echo "unknown option"
        show_help 1
esac
`

	SingleTemplates = TemplateCollection{
		"Copyright": TemplateDesc{
			Description: "Copyright for every sandbox script",
			Notes:       "",
			Contents:    globals.ShellScriptCopyright,
		},
		"replication_options": TemplateDesc{
			Description: "Replication options for my.cnf",
			Notes:       "",
			Contents:    replicationOptions,
		},
		"semisync_master_options": TemplateDesc{
			Description: "master semi-synch options for my.cnf",
			Notes:       "",
			Contents:    semisyncMasterOptions,
		},
		"semisync_slave_options": TemplateDesc{
			Description: "slave semi-synch options for my.cnf",
			Notes:       "",
			Contents:    semisyncSlaveOptions,
		},
		"gtid_options_56": TemplateDesc{
			Description: "GTID options for my.cnf 5.6.x",
			Notes:       "",
			Contents:    gtidOptions56,
		},
		"gtid_options_57": TemplateDesc{
			Description: "GTID options for my.cnf 5.7.x and 8.0",
			Notes:       "",
			Contents:    gtidOptions57,
		},
		"repl_crash_safe_options": TemplateDesc{
			Description: "Replication crash safe options",
			Notes:       "",
			Contents:    replCrashSafeOptions,
		},
		"expose_dd_tables": TemplateDesc{
			Description: "Commands needed to enable data dictionary table usage",
			Notes:       "",
			Contents:    exposeDdTables,
		},
		"init_db_template": TemplateDesc{
			Description: "Initialization template for the database",
			Notes:       "This should normally run only once",
			Contents:    initDbTemplate,
		},
		"start_template": TemplateDesc{
			Description: "starts the database in a single sandbox (with optional mysqld arguments)",
			Notes:       "",
			Contents:    startTemplate,
		},
		"use_template": TemplateDesc{
			Description: "Invokes the MySQL client with the appropriate options",
			Notes:       "",
			Contents:    useTemplate,
		},
		"use_admin_template": TemplateDesc{
			Description: "Invokes the MySQL client as admin",
			Notes:       "For MySQL 8.0.14+",
			Contents:    useAdminTemplate,
		},
		"sysbench_template": TemplateDesc{
			Description: "Invokes the sysbench tool with custom defined options",
			Notes:       "Requires sysbench to be installed",
			Contents:    sysbenchTemplate,
		},
		"sysbench_ready_template": TemplateDesc{
			Description: "Invokes the sysbench tool with predefined actions",
			Notes:       "Requires sysbench to be installed",
			Contents:    sysbenchReadyTemplate,
		},
		"mysqlsh_template": TemplateDesc{
			Description: "Invokes the MySQL shell with an appropriate URI",
			Notes:       "",
			Contents:    mysqlshTemplate,
		},
		"stop_template": TemplateDesc{
			Description: "Stops a database in a single sandbox",
			Notes:       "",
			Contents:    stopTemplate,
		},
		"clear_template": TemplateDesc{
			Description: "Remove all data from a single sandbox",
			Notes:       "",
			Contents:    clearTemplate,
		},
		"my_cnf_template": TemplateDesc{
			Description: "Default options file for a sandbox",
			Notes:       "",
			Contents:    myCnfTemplate,
		},
		"status_template": TemplateDesc{
			Description: "Shows the status of a single sandbox",
			Notes:       "",
			Contents:    statusTemplate,
		},
		"restart_template": TemplateDesc{
			Description: "Restarts the database (with optional mysqld arguments)",
			Notes:       "",
			Contents:    restartTemplate,
		},
		"send_kill_template": TemplateDesc{
			Description: "Sends a kill signal to the database",
			Notes:       "",
			Contents:    sendKillTemplate,
		},
		"load_grants_template": TemplateDesc{
			Description: "Loads the grants defined for the sandbox",
			Notes:       "",
			Contents:    loadGrantsTemplate,
		},
		"grants_template5x": TemplateDesc{
			Description: "Grants for sandboxes up to 5.6",
			Notes:       "",
			Contents:    grantsTemplate5x,
		},
		"grants_template57": TemplateDesc{
			Description: "Grants for sandboxes from 5.7+",
			Notes:       "",
			Contents:    grantsTemplate57,
		},
		"grants_template8x": TemplateDesc{
			Description: "Grants for sandboxes from 8.0+",
			Notes:       "",
			Contents:    grantsTemplate8x,
		},
		"task_user_grants_template": TemplateDesc{
			Description: "Grants for task user (8.0+)",
			Notes:       "",
			Contents:    grantsTaskUserTemplate,
		},
		"my_template": TemplateDesc{
			Description: "Prefix script to run every my* command line tool",
			Notes:       "",
			Contents:    myTemplate,
		},
		"add_option_template": TemplateDesc{
			Description: "Adds options to the my.sandbox.cnf file and restarts",
			Notes:       "",
			Contents:    addOptionTemplate,
		},
		"show_log_template": TemplateDesc{
			Description: "Shows error log or custom log",
			Notes:       "",
			Contents:    showLogTemplate,
		},
		"show_binlog_template": TemplateDesc{
			Description: "Shows a binlog for a single sandbox",
			Notes:       "",
			Contents:    showBinlogTemplate,
		},
		"show_relaylog_template": TemplateDesc{
			Description: "Show the relaylog for a single sandbox",
			Notes:       "",
			Contents:    showRelaylogTemplate,
		},
		"test_sb_template": TemplateDesc{
			Description: "Tests basic sandbox functionality",
			Notes:       "",
			Contents:    testSbTemplate,
		},
		"sb_locked_template": TemplateDesc{
			Description: "locked sandbox script",
			Notes:       "This script is replacing 'clear' when the sandbox is locked",
			Contents:    sbLockedTemplate,
		},
		"after_start_template": TemplateDesc{
			Description: "commands to run after the database started",
			Notes:       "This script does nothing. You can change it and reuse through --use-template",
			Contents:    afterStartTemplate,
		},
		"sb_include_template": TemplateDesc{
			Description: "Common variables and routines for sandboxes scripts",
			Notes:       "",
			Contents:    sbIncludeTemplate,
		},
		"connection_info_sql": TemplateDesc{
			Description: "connection info to replicate from this sandbox",
			Notes:       "",
			Contents:    connectionInfoSql,
		},
		"connection_info_conf": TemplateDesc{
			Description: "connection info to replicate from this sandbox (.conf)",
			Notes:       "",
			Contents:    ConnectionInfoConf,
		},
		"connection_info_json": TemplateDesc{
			Description: "connection info to replicate from this sandbox (.json)",
			Notes:       "",
			Contents:    ConnectionInfoJson,
		},
		"replicate_from": TemplateDesc{
			Description: "starts replication from another sandbox",
			Notes:       "",
			Contents:    replicateFromTemplate,
		},
		"clone_connection_sql": TemplateDesc{
			Description: "connection info to clone from this sandbox",
			Notes:       "",
			Contents:    cloneConnectionSql,
		},
		"clone_from": TemplateDesc{
			Description: "clone from another sandbox",
			Notes:       "",
			Contents:    cloneFromTemplate,
		},
		"metadata_template": TemplateDesc{
			Description: "shows data about the sandbox",
			Notes:       "",
			Contents:    metadataTemplate,
		},
	}
	MockTemplates = TemplateCollection{
		"no_op_mock_template": TemplateDesc{
			Description: "mock script that does nothing",
			Notes:       "Used for internal tests",
			Contents:    noOpMockTemplate,
		},
		"mysqld_safe_mock_template": TemplateDesc{
			Description: "mock script for mysqld_safe",
			Notes:       "Used for internal tests",
			Contents:    mysqldSafeMockTemplate,
		},
		"tidb_mock_template": TemplateDesc{
			Description: "mock script for tidb-server",
			Notes:       "Used for internal tests",
			Contents:    tidbMockTemplate,
		},
	}

	AllTemplates = AllTemplateCollection{
		"mock":        MockTemplates,
		"single":      SingleTemplates,
		"tidb":        TidbTemplates,
		"import":      ImportTemplates,
		"multiple":    MultipleTemplates,
		"replication": ReplicationTemplates,
		"group":       GroupTemplates,
		"pxc":         PxcTemplates,
		"ndb":         NdbTemplates,
	}
)

func FillMockTemplates() error {
	data := defaults.DefaultsToMap()
	for name, template := range MockTemplates {
		tempString, err := common.SafeTemplateFill(name, template.Contents, data)
		if err != nil {
			return fmt.Errorf("error initializing mock template %s", name)
		}
		MockTemplates[name] = TemplateDesc{
			Description:    template.Description,
			TemplateInFile: template.TemplateInFile,
			Notes:          template.Notes,
			Contents:       tempString,
		}
	}
	return nil
}

func init() {
	// The command "dbdeployer defaults template show templateName"
	// depends on the template names being unique across all collections.
	// This initialisation routine will ensure that there are no duplicates.
	var seen = make(map[string]bool)
	for collName, coll := range AllTemplates {
		for name := range coll {
			_, ok := seen[name]
			if ok {
				// name already exists:
				fmt.Printf("Duplicate template %s found in %s\n", name, collName)
				os.Exit(1)
			}
			seen[name] = true
		}
	}
}
